package com.netcracker.util.dao.impl;

import com.netcracker.db.exception.DBException;
import com.netcracker.db.DBManager;
import com.netcracker.entity.record.AuthorRecord;
import com.netcracker.util.dao.AuthorDAO;
import com.netcracker.entity.Author;
import com.netcracker.util.dao.DAOFacade;
import com.netcracker.util.dao.closer.DAOCloseHelper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AuthorDAOImpl extends DAOCloseHelper implements AuthorDAO {
    protected static final String AUTHOR_ID_VALUE = "AuthorId";
    protected static final String AUTHOR_NAME_VALUE = "FullName";
    protected static final String AUTHOR_LANGUAGE_VALUE = "LanguageId";

    protected static final String INSERT_AUTHOR_SQL = "INSERT INTO AUTHORS VALUES(AuthorSequence.NEXTVAL,?,?)";
    protected static final String UPDATE_AUTHOR_SQL = "UPDATE AUTHORS SET FullName = ?, LanguageId = ? WHERE AuthorId = ?";
    protected static final String DELETE_AUTHOR_SQL = "DELETE FROM AUTHORS WHERE AuthorId = ?";
    protected static final String SELECT_AUTHOR_BY_ID_SQL = "SELECT AuthorID, FullName, LanguageId FROM AUTHORS WHERE AuthorId = ?";
    protected static final String SELECT_AUTHOR_BY_NAME_SQL = "Select AuthorID, FullName, LanguageId FROM AUTHORS "
            + "WHERE UPPER(FullName) LIKE ?";
    protected static final String SELECT_AUTHORS_SQL = "SELECT AuthorID, FullName, LanguageId FROM AUTHORS";
    protected static final String SELECT_COUNT_SQL = "SELECT COUNT(*) FROM AUTHORS";
    protected static final String FIND_BOOK_AUTHORS_SQL = "SELECT a.AuthorID, a.FullName, a.LanguageId FROM BookAuthor ba "
            + "JOIN Authors a ON ba.AuthorId = a.AuthorID WHERE ba.BookId = ?";

    private DBManager dbManager;
    private DAOFacade daoFacade;
    
    public AuthorDAOImpl(DBManager dbManager, DAOFacade daoFacade) {
        this.dbManager = dbManager;
        this.daoFacade = daoFacade;
    }

    @Override
    public void setDAOFacade(DAOFacade daoFacade) {
        this.daoFacade = daoFacade;
    }
    
    @Override
    public void setDBManager(DBManager dbManager) {
        this.dbManager = dbManager;
    }
    
    @Override
    public void create(AuthorRecord author) throws DBException{
        PreparedStatement preparedStatement = null;
        try
        {
            dbManager.commit();
            preparedStatement = dbManager.prepareStatement(INSERT_AUTHOR_SQL);
            preparedStatement.setString(1, author.getFullName());
            preparedStatement.setInt(2, author.getLanguageID());
            preparedStatement.executeUpdate();
            dbManager.commit();
        }
        catch (SQLException ex)
        {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
              closeStatement(preparedStatement);
        }
    }

    @Override
    public void edit(AuthorRecord author) throws DBException{
       PreparedStatement preparedStatement = null;
        try
        {
            dbManager.commit();
            preparedStatement = dbManager.prepareStatement(UPDATE_AUTHOR_SQL);
            preparedStatement.setString(1, author.getFullName());
            preparedStatement.setInt(2, author.getLanguageID());
            preparedStatement.setInt(3, author.getId());
            preparedStatement.executeUpdate();
            dbManager.commit();
        }catch(SQLException ex)
        {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
             closeStatement(preparedStatement);
        }
    }

    @Override
    public void remove(int id) throws DBException {
        PreparedStatement preparedStatement = null;
        try
        {
            dbManager.commit();
            preparedStatement = dbManager.prepareStatement(DELETE_AUTHOR_SQL);
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();
            dbManager.commit();
        }catch(SQLException ex)
        {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
             closeStatement(preparedStatement);
        }
    }

    @Override
    public Author find(int id) throws DBException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Author author = null;
        try
        {
            preparedStatement = dbManager.prepareStatement(SELECT_AUTHOR_BY_ID_SQL);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                author = getAuthorEntity(resultSet);
            }

        }catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally {
            closeResultSet(resultSet);
            closeStatement(preparedStatement);
        }
        return author;
    }

    @Override
    public List<Author> findAuthorByName(String name) throws DBException {
        PreparedStatement preparedStatement = null;
        try
        {
            preparedStatement = dbManager.prepareStatement(SELECT_AUTHOR_BY_NAME_SQL);
            preparedStatement.setString(1, "%"+name.toUpperCase()+"%");
            return getAuthors(preparedStatement);
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public List<Author> findAll() throws DBException {
        ResultSet resultSet = dbManager.execSQL(SELECT_AUTHORS_SQL);
        List<Author> authors = new ArrayList<Author>();
        try
        {
            while (resultSet.next())
            {
                authors.add(getAuthorEntity(resultSet));
            }
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally {
            closeResultSet(resultSet);
        }
        return authors;
    }

    @Override
    public int count() throws DBException {
        int count = 0;
        ResultSet resultSet = null;
        try
        {
            resultSet = dbManager.execSQL(SELECT_COUNT_SQL);
            while (resultSet.next())
            {
                count = resultSet.getInt(1);
            }
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally {
             closeResultSet(resultSet);
        }
        return count;

    }

    @Override
    public List<Integer> getBookAuthorsIds(int bookId) throws DBException {
        PreparedStatement preparedStatement = null;
        List<Integer> authorIds = new ArrayList<Integer>();
        ResultSet resultSet = null;
        try
        {
            preparedStatement = dbManager.prepareStatement(FIND_BOOK_AUTHORS_SQL);
            preparedStatement.setInt(1, bookId);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                authorIds.add(resultSet.getInt(AUTHOR_ID_VALUE));
            }
        }
        catch(SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeResultSet(resultSet);
            closeStatement(preparedStatement);
        }
        return authorIds;
    }

    @Override
    public List<Author> findBookAuthors(int bookID) throws DBException {
        PreparedStatement preparedStatement = null;
        try
        {
            preparedStatement = dbManager.prepareStatement(FIND_BOOK_AUTHORS_SQL);
            preparedStatement.setInt(1, bookID);
            return getAuthors(preparedStatement);
        }
        catch(SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeStatement(preparedStatement);
        }
    }

    private Author getAuthorEntity(ResultSet resultSet) throws SQLException, DBException
    {
        Author author = new Author();
        author.setId(resultSet.getInt(AUTHOR_ID_VALUE));
        author.setFullName(resultSet.getString(AUTHOR_NAME_VALUE));
        author.setLanguage(daoFacade.getLanguageDAO().find(resultSet.getInt(AUTHOR_LANGUAGE_VALUE)));
        return author;
    }

    private List<Author> getAuthors(PreparedStatement preparedStatement) throws DBException {
        List<Author> authors = new ArrayList<Author>();
        ResultSet resultSet = null;
        try
        {
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                authors.add(getAuthorEntity(resultSet));
            }
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeResultSet(resultSet);
        }

        return authors;
    }
}
